package Com.Hupochuan.Dal.DBManager;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;

import Com.Hupochuan.Dal.DBLink.DBLink;
import Com.Tester.Tester;

public class DBManager<Vo>{
	/*
	 * 泛型对象
	 */ 
	private Class<Vo> voClass;
	public DBManager(Class<Vo> voClass){
		this.voClass = voClass;
	}

	/*
	 * 执行SQL语句，可以进行增、删、改的操作 返回影响行数
	 */
	public int updateSql(String sql,Hashtable<String,Object> paraHash) {
		SqlSwitchReturnVo returnVo = this.sqlSwitch(sql, paraHash);
		return this.executeUpdateSql(returnVo.sql, returnVo.paraList);
	}
	
	/*
	 * 执行查询SQL 返回模型列表
	 */
	public List<Vo> querySqlReturnVoList(String sql,Hashtable<String,Object> paraHash) {
		SqlSwitchReturnVo returnVo = this.sqlSwitch(sql, paraHash);
		return this.executeQuerySqlReturnVoList(returnVo.sql, returnVo.paraList);
	}
	
	/*
	 * 执行查询SQL 返回模型
	 */
	public Vo querySqlReturnVo(String sql,Hashtable<String,Object> paraHash) {
		SqlSwitchReturnVo returnVo = this.sqlSwitch(sql, paraHash);
		List<Vo> list = this.executeQuerySqlReturnVoList(returnVo.sql, returnVo.paraList);
		return list.size() == 0 ? null : list.get(0);
	}
	
	/*
	 * 执行SQL 返回Object
	 */
	public Object querySqlReturnObject(String sql,Hashtable<String,Object> paraHash) {
		SqlSwitchReturnVo returnVo = this.sqlSwitch(sql, paraHash);
		return this.executeQuerySqlReturnObject(returnVo.sql, returnVo.paraList);
	}
	
	/*
	 * 执行查询SQL 返回数字
	 */
	public long querySqlReturnLong(String sql,Hashtable<String,Object> paraHash){
		SqlSwitchReturnVo returnVo = this.sqlSwitch(sql, paraHash);
		Object longNum = this.executeQuerySqlReturnObject(returnVo.sql, returnVo.paraList);
		return longNum == null ? 0 : Integer.parseInt(longNum.toString());
	}
	
	/*
	 * 执行查询SQL 返回字符串
	 */
	public String executeQuerySqlReturnString(String sql,Hashtable<String,Object> paraHash){
		SqlSwitchReturnVo returnVo = this.sqlSwitch(sql, paraHash);
		return (String)this.executeQuerySqlReturnObject(returnVo.sql,returnVo.paraList);
	}
	
	/*
	 * 执行查询SQL 返回时间对象
	 */
	public Timestamp executeQuerySqlReturnTimestamp(String sql){
		return (Timestamp)this.executeQuerySqlReturnObject(sql,new Vector<Object>());
	}
	
	/*
	 * 执行SQL语句，可以进行增、删、改的操作 返回影响行数
	 */
	private int executeUpdateSql(String sql, List<Object> paraList) {
		Connection conn = null;
		PreparedStatement ps = null;
		int count = 0;
		try {
			conn = DBLink.getObject().getConn();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < paraList.size(); i++) {
				ps.setObject(i + 1, paraList.get(i));
			}
			//打印测试
			Tester.print("修改sql语句:"+ps.toString());
			count = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBLink.getObject().close(null, ps, conn);
		}
		return count;
	}
	
	/*
	 * 执行查询SQL 返回模型列表
	 */
	private List<Vo> executeQuerySqlReturnVoList(String sql,List<Object> paraList) {
		
		//创建模型列表对象
		List<Vo> voObjList = new Vector<Vo>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBLink.getObject().getConn();
			ps= conn.prepareStatement(sql);
			for (int i = 0; i < paraList.size(); i++) {
				ps.setObject(i + 1, paraList.get(i));
			}
			//打印测试
			Tester.print("查询sql语句:"+ps.toString());
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = (ResultSetMetaData)rs.getMetaData();
			
			while(rs.next()){
				//创建Vo对象
				Vo voObj = (Vo)voClass.newInstance();
				
				//循环处理每行的每个字段
				for(int i = 0 ; i < rsmd.getColumnCount() ; i++){
					
					//获得此字段名
					String ColumnName = rsmd.getColumnName(i + 1);
				
					//获得此字段值
					Object ColumnValue = rs.getObject(i + 1);
					
					//获得此字段的类型
					String ColumnType = rsmd.getColumnClassName(i + 1);
					
					//创建方法名字符串的容器 并根据字段名 转换出方法名
					StringBuffer methodNameBuff  = new StringBuffer("set");
					methodNameBuff.append(ColumnName.substring(0, 1).toUpperCase());
					methodNameBuff.append(ColumnName.substring(1, ColumnName.length())); 
					
					/*
					Tester.print("方法名:" + methodNameBuff.toString());
					Tester.print("列类型:" + ColumnType);
					Tester.print("列类型:" + rsmd.getColumnTypeName(i + 1));
					*/
					
					//获得此字段对应模型的方法对象
					//Method method = voClass.getMethod(methodNameBuff.toString());
					Method method = voClass.getMethod(methodNameBuff.toString(),Class.forName(ColumnType));
					
					//往方法中注入本字段的数据
					method.invoke(voObj,ColumnValue);
				}
				voObjList.add(voObj);
			}
			
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DBLink.getObject().close(rs, ps, conn);
		}
		
		//返回模型列表
		return voObjList;
	}
	
	/*
	 * 执行SQL 返回Object
	 */
	private Object executeQuerySqlReturnObject(String sql,List<Object> paraList) {
	
		//设置要返回的Object
		Object returnObject = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = DBLink.getObject().getConn();
			ps= conn.prepareStatement(sql);
			for (int i = 0; i < paraList.size(); i++) {
				ps.setObject(i + 1, paraList.get(i));
			}
			//打印测试
			Tester.print("查询sql语句:"+ps.toString()+"*/");
			rs = ps.executeQuery();
			if(rs.next()){
				returnObject = rs.getObject(1);
			}
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		return returnObject;
	}
	
	/*
	 * 私有类 专门用于下面方法返回参数用
	 */
	private class SqlSwitchReturnVo{
		String sql;
		List<Object> paraList;
	}
	
	/*
	 * sql转换方法
	 */
	private SqlSwitchReturnVo sqlSwitch(String sql,Hashtable<String,Object> paraHash) {
		//创建返回模型
		SqlSwitchReturnVo returnVo = new SqlSwitchReturnVo();
		//用于存放转换后的sql语句的 String缓冲器
		StringBuffer sqlBuffer = new StringBuffer();
		//用于存放转换后的sql参数列表
		List<Object> paraList = new Vector<Object>();
		
		//打印测试
		//Tester.print("转换后的sql语句:"+sql);
		
		//用$符分割 并遍历
		for(String aSplitSql : sql.split("\\$")){
			//左大括弧坐标
			int bracketL = aSplitSql.indexOf("{");
			//如果找到左大括弧坐标
			if(bracketL!=-1){
				//右大括弧坐标
				int bracketR = aSplitSql.indexOf("}");
				//带{}的关键字 用于替换
				String replaceKey = aSplitSql.substring(bracketL,bracketR+1);
				//不带{}的关键字 用于查找Hashtable
				String hashKey = replaceKey.substring(1,replaceKey.length()-1);
				//将原来的{key}替换为?
				aSplitSql = aSplitSql.replace(replaceKey, "?");
				//将key添加入新参数列表
				paraList.add(paraHash.get(hashKey));
			}
			sqlBuffer.append(aSplitSql);
		}
		
		//打印测试
		//Tester.print("转换后的sql语句:"+sqlBuffer.toString());
		returnVo.sql = sqlBuffer.toString();
		returnVo.paraList = paraList;

		return returnVo;
	}
}
